package com.wstuo.itsm.request.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;
import org.hibernate.FetchMode;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;

import com.wstuo.common.tools.dto.StatResultDTO;
import com.wstuo.itsm.request.entity.Request;
import com.wstuo.itsm.request.dto.RequestQueryDTO;
import com.wstuo.common.customForm.dao.IFieldDAO;
import com.wstuo.common.customForm.entity.Field;
import com.wstuo.common.dao.BaseDAOImplHibernate;
import com.wstuo.common.dto.PageDTO;
import com.wstuo.common.proxy.dao.IProxyDAO;
import com.wstuo.common.proxy.entity.AuthorizeProxy;
import com.wstuo.common.util.DaoUtils;
import com.wstuo.common.util.MathUtils;
import org.hibernate.type.StandardBasicTypes;

/**
 * class RequestDAO.
 * 
 * @author QXY
 * 
 */
public class RequestDAO extends BaseDAOImplHibernate<Request> implements
		IRequestDAO {
	@Autowired
	private IProxyDAO proxyDAO;
	@Autowired
	private IFieldDAO fieldDAO;
	/**
	 * 分页查询请求信息
	 * 
	 * @param qdto
	 * @param sidx
	 * @param sord
	 * @return PageDTO
	 */
	public PageDTO findPager(RequestQueryDTO qdto, String sidx, String sord) {
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		int start = 0;
		int limit = 0;
		if (qdto != null) {
			start = qdto.getStart();
			limit = qdto.getLimit();
			// 创建条件别名
			dc = createConditionAlias(qdto, dc);
			// 查询条件
			dc = searchCondition(qdto, dc);
			// 公共的排序方法
			dc = DaoUtils.orderBy(sidx, sord, dc);
		}
		return super.findPageByCriteria(dc, start, limit);
	}

	/**
	 * 查询请求信息
	 * 
	 * @param qdto
	 * @param sidx
	 * @param sord
	 * @return List<Request>
	 */
	@SuppressWarnings("rawtypes")
	public List findRequest(RequestQueryDTO qdto, String sidx, String sord) {
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		int start = 0;
		int limit = 0;
		if (qdto != null) {
			start = qdto.getStart();
			limit = qdto.getLimit();
			// 创建条件别名
			dc = createConditionAlias(qdto, dc);
			// 查询条件
			dc = searchCondition(qdto, dc);
			// 公共的排序方法
			dc = DaoUtils.orderBy(sidx, sord, dc);
		}
		return super.getHibernateTemplate().findByCriteria(dc, start, limit);
	}

	/**
	 * 根据条件统计
	 * @param qdto
	 * @return
	 */
	public Integer countRequestByType(RequestQueryDTO qdto){
		int result = 0;
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		if (qdto != null) {
			// 创建条件别名
			dc = createConditionAlias(qdto, dc);
			// 查询条件
			dc = searchCondition(qdto, dc);
			result = super.statCriteria(dc);
		}
		return result;
	}
	
	
	
	
	/**
	 * 查询条件
	 * 
	 * @param qdto
	 * @param dc
	 * @return
	 */
	private DetachedCriteria searchCondition(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 基础属性
		dc = searchBaseAttribute(qdto, dc);
		// 查询关联
		dc = searchRelation(qdto, dc);
		// 根据时间查询
		dc = searchByTime(qdto, dc);

		// 查询类型
		dc = searchCountQueryType(qdto, dc);
		
		//扩展字段查询
		//dc = searchCustomField(qdto, dc);
		return dc;
	}
	private DetachedCriteria searchCustomField(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		dc.add(Restrictions .sqlRestriction("request2 LIKE(?)", '3', StandardBasicTypes.CHAR_ARRAY));
		return dc;
	}

	@SuppressWarnings("deprecation")
	private DetachedCriteria createConditionAlias(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 关联创建人
		if (RequestQueryDTO.isCreateAliasForCreateBy(qdto)
				|| StringUtils.hasText(qdto.getCreatedByFullName())) {
			dc.createAlias("createdBy", "cby");
		}
		
		// 指派给我的是否为空// 统计类型是否为空
		if (RequestQueryDTO.isCeateAliasForTechnician(qdto)
				|| RequestQueryDTO.isMatchToSearchLoginNameCondition(qdto)) {
			dc.createAlias("technician", "t");
		}
		if (MathUtils.isPositive(qdto.getStatusNo()) || StringUtils.hasText(qdto.getStatusName())
				|| RequestQueryDTO.isContainQueryType(qdto)
				|| (qdto.getGroupField()!=null && qdto.getGroupField().equals("status"))
				|| (qdto.getStatusNo()!=null && qdto.getStatusNo() == 0 && StringUtils.hasText(qdto.getLabel()) && "unknown".equals(qdto.getLabel()))) {// 根据查询类型查询
			dc.createAlias("status", "st", CriteriaSpecification.LEFT_JOIN);
		}
		if (RequestQueryDTO.isCreateAliasForOwner(qdto)) {// 负责人
			dc.createAlias("owner", "on");
		}
		dc.createAlias("requestCategory", "rc", CriteriaSpecification.LEFT_JOIN);
		if (MathUtils.isPositive(qdto.getImodeNo())) {// 来源
			dc.createAlias("imode", "im");
		}
		if (MathUtils.isPositive(qdto.getPriorityNo()) 
				|| StringUtils.hasText(qdto.getPriorityName())
				|| (qdto.getGroupField()!=null && qdto.getGroupField().equals("priority"))
				|| (qdto.getPriorityNo()!=null && qdto.getPriorityNo() == 0 && StringUtils.hasText(qdto.getLabel()) && "unknown".equals(qdto.getLabel()))) {// 优先级
			dc.createAlias("priority", "pr", CriteriaSpecification.LEFT_JOIN);
		}
		if (MathUtils.isPositive(qdto.getLevelNo())) {// 复杂程度
			dc.createAlias("level", "le");
		}
		if (MathUtils.isPositive(qdto.getSeriousnessNo())) {// 紧急度
			dc.createAlias("seriousness", "se");
		}
		if (MathUtils.isPositive(qdto.getEffectRangeNo())) {// 影响范围
			dc.createAlias("effectRange", "ef");
		}
		
		if (MathUtils.isPositive(qdto.getLocationId())) {// 地点
			dc.createAlias("location", "lc");
		}
		if (MathUtils.isPositive(qdto.getServiceDirIds())) {// 服务目录
			dc.createAlias("serviceDirectory", "scd");
		} 
		
		
		if (MathUtils.isPositive(qdto.getAssigneeGroupNo())||
				StringUtils.hasText(qdto.getCountQueryType())&&"assigneeGroupRequest".equals(qdto.getCountQueryType())) {
			dc.createAlias("assigneeGroup", "ag");
		}
		if (MathUtils.isPositive(qdto.getCiId())) {
			dc.createAlias("relatedConfigureItems", "config");
		}
		if (qdto.getSlaStateNo() != null 
				|| StringUtils.hasText(qdto.getSlaStateName())
				|| (qdto.getGroupField()!=null && qdto.getGroupField().equals("slaState"))
				|| (qdto.getSlaStateNo()!=null && qdto.getSlaStateNo() == 0 && StringUtils.hasText(qdto.getLabel()) && "unknown".equals(qdto.getLabel()))) {
			dc.createAlias("slaState", "ss", CriteriaSpecification.LEFT_JOIN);
		}
		return dc;
	}

	/**
	 * 基础属性
	 * 
	 * @param qdto
	 * @param dc
	 */

	private DetachedCriteria searchBaseAttribute(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		
		if (qdto.getIds() != null && qdto.getIds().length > 0) {
			dc.add(Restrictions.in("eno", qdto.getIds()));
		}
		// 所属客户
		if (qdto.getCompanyNos() != null && qdto.getCompanyNos().length > 0) {
			dc.add(Restrictions.in("companyNo", qdto.getCompanyNos()));
		}
		if (qdto.getCompanyNo() != null) {
			dc.add(Restrictions.in("companyNo",
					new Long[] { qdto.getCompanyNo() }));
		}

		if (StringUtils.hasText(qdto.getRequestCode())) {// 编码
			dc.add(Restrictions.ilike("requestCode", qdto.getRequestCode(),
					MatchMode.ANYWHERE));
		}
		if (StringUtils.hasText(qdto.getKeyWord())) {// 关键字
			dc.add(Restrictions.ilike("keyWord", qdto.getKeyWord(),
					MatchMode.ANYWHERE));
		}
		if (StringUtils.hasText(qdto.getEtitle())) {// 标题
			dc.add(Restrictions.ilike("etitle", qdto.getEtitle(),
					MatchMode.ANYWHERE));
		}
		if (StringUtils.hasText(qdto.getEdesc())) {//描述
			dc.add(Restrictions.ilike("edesc", qdto.getEdesc(),
					MatchMode.ANYWHERE));
		}
		return dc;
	}

	/**
	 * 根据数据字典查询
	 * 
	 * @param qdto
	 * @param dc
	 */
	private DetachedCriteria searchDatadictionary(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		dc = searchCategory(qdto, dc);
		if (MathUtils.isPositive(qdto.getStatusNo())) {// 状态
			dc.add(Restrictions.eq("st.dcode", qdto.getStatusNo()));
		}else if (qdto.getStatusNo()!=null && qdto.getStatusNo() == 0 && StringUtils.hasText(qdto.getLabel()) && "unknown".equals(qdto.getLabel())) {
			dc.add(Restrictions.isNull("st.dcode"));
		}
		if (StringUtils.hasText(qdto.getStatusName())){
			dc.add(Restrictions.eq("st.dname", qdto.getStatusName()));
		}
		if (MathUtils.isPositive(qdto.getImodeNo())) {// 来源
			dc.add(Restrictions.eq("im.dcode", qdto.getImodeNo()));
		}
		if(MathUtils.isPositive(qdto.getLocationId())){
			dc.add(Restrictions.eq("lc.eventId", qdto.getLocationId()));
		}
		
		if(MathUtils.isPositive(qdto.getServiceDirIds())){
			dc.add(Restrictions.eq("scd.eventId", qdto.getServiceDirIds()));
		}
		
		if (MathUtils.isPositive(qdto.getPriorityNo())) {// 优先级
			dc.add(Restrictions.eq("pr.dcode", qdto.getPriorityNo()));
		}else if (qdto.getPriorityNo()!=null && qdto.getPriorityNo() == 0 && StringUtils.hasText(qdto.getLabel()) && "unknown".equals(qdto.getLabel())) {
			dc.add(Restrictions.isNull("pr.dcode"));
		}
		if (StringUtils.hasText(qdto.getPriorityName())){
			dc.add(Restrictions.eq("pr.dname", qdto.getPriorityName()));
		}
		if (MathUtils.isPositive(qdto.getLevelNo())) {// 复杂程度
			dc.add(Restrictions.eq("le.dcode", qdto.getLevelNo()));
		}
		if (MathUtils.isPositive(qdto.getSeriousnessNo())) {// 紧急度
			dc.add(Restrictions.eq("se.dcode", qdto.getSeriousnessNo()));
		}
		if (MathUtils.isPositive(qdto.getEffectRangeNo())) {// 影响范围
			dc.add(Restrictions.eq("ef.dcode", qdto.getEffectRangeNo()));
		}
		if (qdto.getSlaStateNo() != null && qdto.getSlaStateNo() != 0) {// SLA状态
			dc.add(Restrictions.eq("ss.dcode", qdto.getSlaStateNo()));
		}else if(qdto.getSlaStateNo()!=null && qdto.getSlaStateNo() == 0 && StringUtils.hasText(qdto.getLabel()) && "unknown".equals(qdto.getLabel())){
			dc.add(Restrictions.isNull("ss.dcode"));
		}
		if (StringUtils.hasText(qdto.getSlaStateName())){
			dc.add(Restrictions.eq("ss.dname", qdto.getSlaStateName()));
		}
		return dc;
	}

	/**
	 * 查询关联
	 * 
	 * @param qdto
	 * @param dc
	 * @param alias_owner
	 * @param alias_assignee
	 * @param alias_createdBy
	 */
	private DetachedCriteria searchRelation(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 查询数据字典
		dc = searchDatadictionary(qdto, dc);
		// 查找请求人相关的
		dc = searchRequestorRelation(qdto, dc);
		// 指派组
		if (MathUtils.isPositive(qdto.getAssigneeGroupNo())) {
			dc.add(Restrictions.eq("ag.orgNo", qdto.getAssigneeGroupNo()));
		}
		// 查询相关人员
		dc = searchRelateHuman(qdto, dc);
		// 关联配置项
		if (MathUtils.isPositive(qdto.getCiId())) {
			dc.add(Restrictions.eq("config.ciId", qdto.getCiId()));
		}
		return dc;
	}

	/**
	 * 查询请求人相关的
	 * 
	 * @param qdto
	 * @param dc
	 * @param alias_createdBy
	 * @return
	 */
	private DetachedCriteria searchRequestorRelation(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 请求人
		if (null != qdto.getCreatedByFullNames()) {
			dc.add(Restrictions.in("cby.fullName", qdto.getCreatedByFullNames()));
		}
		if (null != qdto.getCreatedByNames()) {
			dc.add(Restrictions.in("cby.loginName", qdto.getCreatedByNames()));

		}
		if (StringUtils.hasText(qdto.getRequestorMoblie())) {
			dc.add(Restrictions.ilike("cby.moblie", qdto.getRequestorMoblie(),
					MatchMode.ANYWHERE));
		}
		if (StringUtils.hasText(qdto.getRequestorPhone())) {
			dc.add(Restrictions.ilike("cby.phone", qdto.getRequestorPhone(),
					MatchMode.ANYWHERE));
		}
		if("myGroupProposedRequest".equals(qdto.getCountQueryType()) || MathUtils.isPositive(qdto.getRequestorOrgNo())){
			dc.createAlias("cby.orgnization", "org");
		}
		if (MathUtils.isPositive(qdto.getRequestorOrgNo())) {// 请求人所在组
			dc.add(Restrictions.eq("org.orgNo",qdto.getRequestorOrgNo()));
		}
		// 我提出的请求
		if (StringUtils.hasText(qdto.getCountQueryType())
				&& "myProposedRequest".equals(qdto.getCountQueryType())
				&& qdto.getCurrentUser() != null) {
			dc.add(Restrictions.eq("cby.loginName", qdto.getCurrentUser()));
		}
		// 我们组提出的请求
		if (StringUtils.hasText(qdto.getCountQueryType())
				&& "myGroupProposedRequest".equals(qdto.getCountQueryType())) {
			dc.add(Restrictions.eq("org.orgNo", qdto.getOrgNo()));
		}
		return dc;
	}

	/**
	 * 查询相关人员
	 * 
	 * @param qdto
	 * @param dc
	 * @param alias_owner
	 * @param alias_assignee
	 * @param alias_createdBy
	 */
	private DetachedCriteria searchRelateHuman(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 负责人
		if (null != qdto.getOwnerNames()) {
			dc.add(Restrictions.in("on.loginName", qdto.getOwnerNames()));
		}
		// 指派人
		if (null != qdto.getAssigneeFullNames()) {// 指派人
			dc.add(Restrictions.in("t.fullName", qdto.getAssigneeFullNames()));
		}
		if (null != qdto.getAssigneeNames()) {// 指派人
			dc.add(Restrictions.in("t.loginName", qdto.getAssigneeNames()));
		}
		// 负责人
		if (StringUtils.hasText(qdto.getOwnerFullName())) {
			dc.add(Restrictions.in("on.fullName", qdto.getOwnerFullNames()));
		}
		return dc;
	}

	/**
	 * 根据时间查询
	 * 
	 * @param qdto
	 * @param dc
	 */
	private DetachedCriteria searchByTime(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 时间搜索
		if (qdto.getStartTime() != null && qdto.getEndTime() == null) {
			dc.add(Restrictions.ge("createdOn", qdto.getStartTime()));
		}
		if (qdto.getStartTime() == null && qdto.getEndTime() != null) {
			Calendar endTimeCl = new GregorianCalendar();
			endTimeCl.setTime(qdto.getEndTime());
			endTimeCl.set(Calendar.DATE, endTimeCl.get(Calendar.DATE) + 1);
			dc.add(Restrictions.le("createdOn", endTimeCl.getTime()));
		}
		if (qdto.getStartTime() != null && qdto.getEndTime() != null) {
			Calendar endTimeCl = new GregorianCalendar();
			endTimeCl.setTime(qdto.getEndTime());
			endTimeCl.set(Calendar.DATE, endTimeCl.get(Calendar.DATE) + 1);

			dc.add(Restrictions.and(
					Restrictions.le("createdOn", endTimeCl.getTime()),
					Restrictions.ge("createdOn", qdto.getStartTime())));
		}
		return dc;
	}

	/**
	 * 查询类型
	 * 
	 * @param qdto
	 * @param dc
	 */
	private DetachedCriteria searchCountQueryType(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		if (StringUtils.hasText(qdto.getCountQueryType())) {
			// 查询指派的请求
			dc = searchAssignRequest(qdto, dc);
			// 我负责的请求
			if ("myOwnerRequest".equals(qdto.getCountQueryType())
					&& StringUtils.hasText(qdto.getCurrentUser())) {
				dc.add(Restrictions.eq("on.loginName", qdto.getCurrentUser()));
			}
			// 代理给我的请求
			dc = searchProxyToMe(qdto, dc);
			// 根据请求状态查询
			dc = searchByRequestStatus(qdto, dc);
		}
		return dc;
	}

	private DetachedCriteria searchAssignRequest(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 指派给我们组请求
		if ("assigneeGroupRequest".equals(qdto.getCountQueryType())&&qdto.getGroups() != null && qdto.getGroups().length > 0) {
			dc.setFetchMode("assigneeGroup", FetchMode.SELECT);
			 dc.add(Restrictions.in("ag.orgNo", qdto.getGroups()));
		}
		// 指派给我请求
		if ("assigneeToMyRequest".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCurrentUser())) {
			dc.add(Restrictions.eq("t.loginName", qdto.getCurrentUser()));
		}
		return dc;
	}

	/**
	 * 代理给我的请求
	 * 
	 * @param qdto
	 * @param dc
	 * @return
	 */
	private DetachedCriteria searchProxyToMe(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		// 代理给我的请求
		if ("actingToMyRequest".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCurrentUser())) {
			List<AuthorizeProxy> list = proxyDAO.findProxyOrpersonal(
					qdto.getCurrentUser(), null);
			String[] currents = null;
			if (list != null && list.size() > 0) {
				currents = new String[list.size()];
				int i = 0;
				for (AuthorizeProxy ap : list) {
					currents[i] = ap.getProxieduser().getLoginName();
					i++;
				}
			}
			if (currents != null && currents.length > 0) {
				dc.add(Restrictions.in("t.loginName", currents));// 传入查询代理用户
			} else {
				dc.add(Restrictions.eq("t.loginName", "actingToMyRequest"));// 如果不存在代理人，则查询不存在情况
			}
		}
		return dc;
	}

	/**
	 * 根据请求状态查询
	 * 
	 * @param qdto
	 * @param dc
	 * @return
	 */
	private DetachedCriteria searchByRequestStatus(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		if (RequestQueryDTO.isMatchToSearchLoginNameCondition(qdto)) {
			dc.add(Restrictions.eq("t.loginName", qdto.getCurrentUser()));
		}
		if ("nohandle".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCurrentUser())) {// 未处理
			dc.add(Restrictions.eq("st.dno", "request_new"));
		}
		if ("handle".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCurrentUser())) {// 正在处理
			dc.add(Restrictions.not(Restrictions.in("st.dno", new String[] {
					"request_new", "request_complete", "request_close" })));

		}
		if ("complete".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCountQueryType())) {// 已完成
			dc.add(Restrictions.eq("st.dno", "request_complete"));
		}
		if ("close".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCurrentUser())) {// 已关闭
			dc.add(Restrictions.eq("st.dno", "request_close"));
		}
		if ("myNotComprehensiveNotSubmitted".equals(qdto.getCountQueryType())
				&& StringUtils.hasText(qdto.getCurrentUser())) {// 我描述不全、未提交
			dc.add(Restrictions.eq("st.dno",
					"request_notComprehensiveNotSubmitted"));
			dc.add(Restrictions.eq("cby.loginName", qdto.getCurrentUser()));
		}
		return dc;
	}

	/**
	 * 保存请求
	 * 
	 * @param request
	 */
	public void saveRequest(Request request) {
		request.setLastUpdateTime(new Date());
		super.save(request);
	}

	/**
	 * 保存请求
	 * 
	 * @param request
	 */
	@Override
	public Request merge(Request request) {

		request.setLastUpdateTime(new Date());
		return super.merge(request);
	}
	
	
	


	/**
	 * 获取所有请求的条件
	 * @param qdto
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private DetachedCriteria getAllRequestDetachedCriteria(RequestQueryDTO qdto) {
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		if (qdto.getCompanyNos() != null && qdto.getCompanyNos().length > 0) {
			dc.add(Restrictions.in("companyNo", qdto.getCompanyNos()));
		}
		dc.createAlias("requestCategory", "rc", CriteriaSpecification.LEFT_JOIN);
		dc = searchCategory(qdto, dc);
		return dc;
	}

	/**
	 * 请求统计
	 */
	public Integer requestCountStat(RequestQueryDTO qdto,
			String type) {
		DetachedCriteria dc = getAllRequestDetachedCriteria(qdto);
		dc.createAlias("technician", "asg");
		if(StringUtils.hasText(qdto.getLoginName())){
			dc.add(Restrictions.eq("asg.loginName", qdto.getLoginName()));
		}
		dc.createAlias("status", "st");
		if ("nohandle".equals(type)) {// 未处理
			dc.add(Restrictions.eq("st.dno", "request_new"));
		}
		if ("handle".equals(type)) {// 正在处理
			eliminateByRequestStatus(dc);
		}
		if ("complete".equals(type)) {// 已完成
			dc.add(Restrictions.eq("st.dno", "request_complete"));
		}
		if ("close".equals(type)) {// 已关闭
			dc.add(Restrictions.eq("st.dno", "request_close"));
		}
		return super.statCriteria(dc);
	}

	/**
	 * 工程师处理统计
	 * @param queryDTO
	 * @return List<StatResultDTO>
	 */
	@SuppressWarnings("unchecked")
	public List<StatResultDTO> requestStat(RequestQueryDTO queryDTO) {
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);

		dc.createAlias("status", "st");
		dc.add(Restrictions.ne("st.dno", "request_approval"));// 排除审批
		// 排除新建// 排除完成// 排除关闭
		eliminateByRequestStatus(dc);
		dc.createAlias("technician", "as");
		dc.setProjection(Projections.projectionList()
				.add(Projections.groupProperty("as.loginName").as("name"))
				.add(Projections.alias(Projections.rowCount(), "quantity")));

		dc.setResultTransformer(Transformers.aliasToBean(StatResultDTO.class));
		List<StatResultDTO> list = (List<StatResultDTO>) getHibernateTemplate()
				.findByCriteria(dc);
		return list;
	}

	/**
	 * 排除新建,排除完成,排除关闭
	 * 
	 * @param dc
	 */
	private DetachedCriteria eliminateByRequestStatus(DetachedCriteria dc) {
		dc.add(Restrictions.ne("st.dno", "request_new"));
		dc.add(Restrictions.ne("st.dno", "request_complete"));
		dc.add(Restrictions.ne("st.dno", "request_close"));
		return dc;
	}

	/**
	 * 查询长期未解决的请求.
	 * 
	 * @param dateLong
	 *            超出时间
	 * @return List<Request>
	 */
	@SuppressWarnings("unchecked")
	public List<Request> findUnresolved(Long dateLong) {

		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);

		Long compileLong = new Date().getTime() - dateLong;// 比较时间
		dc.add(Restrictions.isNull("requestResolvedTime"));// 未完成
		dc.add(Restrictions.eq("createdProblem", false));// 未创建过问题
		dc.add(Restrictions.lt("maxCompleteTime", compileLong));// 超出时间没响应
		return getHibernateTemplate().findByCriteria(dc);
	}

	/**
	 * 查询没未关闭或SLA状态为空的请求
	 * (并且最多一次只查出50条数据)
	 * @return List<Request>
	 */
	public PageDTO findSLAStatusRequest() {

		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		Disjunction dj = Restrictions.disjunction();
		dc.add(dj);
		dj.add(Restrictions.isNull("closeTime"));// 关闭时间为空
		dj.add(Restrictions.isNull("slaState"));// 状态为空
		dc.createAlias("slaState", "st");
		String[] str = new String[]{"ToBeRespondedOverdue&ToBeSettledOverdue","RespondedOverdue&ToBeSettledOverdue","RespondedOnTime&ToBeSettledOverdue"};
		//且不等于超时未完成
		dc.add(Restrictions.not(Restrictions.in("st.dno",str)));
		return super.findPageByCriteria(dc, 0, 50);
	}

	/**
	 * 根据用户和状态查询请求
	 * 
	 * @param loginName
	 * @return List<Request>
	 */
	@SuppressWarnings("unchecked")
	public List<Request> findReqByUserAndState(String loginName) {

		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);

		dc.createAlias("technician", "asg").add(
				Restrictions.eq("asg.loginName", loginName));
		dc.createAlias("status", "st");
		dc = eliminateByRequestStatus(dc);
		return getHibernateTemplate().findByCriteria(dc);
	}

	/**
	 * 繁忙程度:用户请求统计
	 */
	@SuppressWarnings("rawtypes")
	public List findUserRequestStats(Long categoryId,String belongsGroupIds,List<String> users) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.loginName,b.allCoun,b.scores FROM ");
		sql.append("(SELECT tu.loginName AS loginName FROM Request r ");
		//sql.append("LEFT JOIN Event e ON r.eno=e.eno ");
		sql.append("LEFT JOIN DataDictionaryItems dt ON r.status_dcode=dt.dcode ");
		sql.append("RIGHT JOIN T_User tu ON r.technician_userId=tu.userId ");
		sql.append("LEFT JOIN T_User_Role  tur ON tu.userId = tur.users_userId ");
		sql.append("LEFT JOIN T_User_Organization tuo ON tu.userId = tuo.T_User_userId ");
		sql.append("LEFT JOIN Role rl ON tur.roles_roleId = rl.roleId ");
		sql.append("LEFT JOIN Role_o_resource ror ON rl.roleId = ror.roles_roleId ");
		sql.append("LEFT JOIN o_resource ores ON ror.resources_resNo = ores.resNo ");
		sql.append("WHERE rl.roleCode!='ROLE_ENDUSER' AND rl.roleCode!='ROLE_ITSOP_MANAGEMENT' AND rl.roleCode!='ROLE_APPROVER' AND tu.userState=1 AND tu.holidayStatus =0 ");
		if(categoryId!=null && categoryId!=0){//请求分类权限
			sql.append("AND ores.resCode = 'Request_Category_"+categoryId+"' ");
		}
		if(StringUtils.hasText(belongsGroupIds)){
			sql.append("AND tuo.belongsGroup_orgNo IN ("+belongsGroupIds+") ");
		}
		sql.append("GROUP BY tu.loginName) a ");
		sql.append("LEFT JOIN ");
		sql.append("(SELECT tu2.loginName AS loginName,COUNT(DISTINCT r2.eno) AS allCoun , SUM(ec.scores) AS scores  FROM Request r2 ");
		//sql.append("LEFT JOIN Event e2 ON r2.eno=e2.eno ");
		sql.append("LEFT JOIN Request_EventCategory rec ON r2.eno=rec.Request_eno ");
		sql.append("LEFT JOIN EventCategory ec ON rec.serviceDirectory_EventId=ec.EventId ");
		sql.append("RIGHT JOIN T_User tu2 ON r2.technician_userId=tu2.userId ");
		sql.append("WHERE r2.closeTime IS NULL ");
		sql.append("GROUP BY tu2.loginName) b ");
		sql.append("ON a.loginName = b.loginName ");
		if (users != null && users.size() > 0) {
			StringBuffer loginNames = new StringBuffer();
			for (String u : users) {
				loginNames.append("'"+u+"',");
			}
			sql.append("WHERE a.loginName IN ("+ loginNames.substring(0, loginNames.length() - 1) +") ");
		}
		sql.append("ORDER BY b.scores ASC ,b.allCoun ASC ");
		return super.findPageBySql(sql.toString(), 0, 1).getData();
	}

	/**
	 * 分页并排序查询繁忙程度统计
	 * (条件过于复杂，不能使用hql和dc)
	 */
	public PageDTO findUserRequestStatsBySort(Long[] companyNos,String sidx,String sord,int start ,int limit){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.loginName,a.fullName,b.allCoun,c.handleCoun,d.newCoun ,e.scores FROM ");
		sql = setSqlByUser(sql);
		sql = setSqlByAllCount(companyNos,sql);
		sql = setSqlByHandleCount(companyNos,sql);
		sql = setSqlByNewleCount(companyNos,sql);
		sql = setSqlByScoresSum(companyNos,sql);
		sql = sortHql(sql, sidx, sord);
		return super.findPageBySql(sql.toString(), start, limit);
	}
	/**
	 * 构造请求统计所有用户Sql
	 * @param sql
	 * @return StringBuffer
	 */
	private StringBuffer setSqlByUser(StringBuffer sql){
		sql.append("(SELECT tu.loginName AS loginName, tu.fullName AS fullName FROM Request r ");
		//sql.append("LEFT JOIN Event e ON r.eno=e.eno ");
		sql.append("LEFT JOIN DataDictionaryItems dt ON r.status_dcode=dt.dcode ");
		sql.append("RIGHT JOIN T_User tu ON r.technician_userId=tu.userId ");
		sql.append("LEFT JOIN T_User_Role  tur ON tu.userId = tur.users_userId ");
		sql.append("LEFT JOIN Role rl ON tur.roles_roleId = rl.roleId ");
		sql.append("WHERE rl.roleCode!='ROLE_ENDUSER' AND rl.roleCode!='ROLE_ITSOP_MANAGEMENT' AND rl.roleCode!='ROLE_APPROVER' AND tu.userState=1 AND tu.holidayStatus =0 ");
		sql.append("GROUP BY tu.loginName ,tu.fullName ) a ");
		return sql;
	}

	/**
	 * 构造请求统计所有请求数Sql
	 * @param sql
	 * @return StringBuffer
	 */
	private StringBuffer setSqlByAllCount(Long[] companyNos,StringBuffer sql){
		sql.append("LEFT JOIN ");
		sql.append("(SELECT tu.loginName AS loginName, COUNT(DISTINCT r.eno) AS allCoun FROM Request r ");
		//sql.append("LEFT JOIN Event e ON r.eno=e.eno ");
		sql.append("LEFT JOIN DataDictionaryItems dt ON r.status_dcode=dt.dcode ");
		sql.append("RIGHT JOIN T_User tu ON r.technician_userId=tu.userId ");
		sql.append("LEFT JOIN T_User_Role  tur ON tu.userId = tur.users_userId ");
		sql.append("LEFT JOIN Role rl ON tur.roles_roleId = rl.roleId ");
		sql.append("WHERE rl.roleCode!='ROLE_ENDUSER' AND rl.roleCode!='ROLE_ITSOP_MANAGEMENT' AND rl.roleCode!='ROLE_APPROVER' ");
		if(companyNos!=null&&companyNos.length>0){
			sql.append("AND r.companyNo IN (");
			setCompanyNos(companyNos, sql);
			sql.append(") ");
		}
		sql.append("GROUP BY tu.loginName ,tu.fullName ) b ");
		sql.append("ON a.loginName = b.loginName ");
		return sql;
	}
	/**
	 * set companyNos
	 * @param companyNos
	 * @param sql
	 */
	private StringBuffer setCompanyNos(Long[] companyNos,StringBuffer sql){
		for (int i = 0; i < companyNos.length; i++) {
			Long companyNo = companyNos[i];
			sql.append(companyNo);
			if(i != companyNos.length-1){
				sql.append(",");
			}
		}
		return sql;
	}
	/**
	 * 构造请求统计正在处理请求数Sql
	 * @param sql
	 * @return StringBuffer
	 */
	private StringBuffer setSqlByHandleCount(Long[] companyNos,StringBuffer sql){
		sql.append("LEFT JOIN ");
		sql.append("(SELECT tu2.loginName AS loginName, COUNT(DISTINCT r2.eno) AS handleCoun FROM Request r2 ");
		//sql.append("LEFT JOIN Event e2 ON r2.eno=e2.eno ");
		sql.append("LEFT JOIN DataDictionaryItems dt2 ON r2.status_dcode=dt2.dcode ");
		sql.append("RIGHT JOIN T_User tu2 ON r2.technician_userId=tu2.userId ");
		sql.append("WHERE dt2.dno != 'request_new' AND dt2.dno != 'request_complete' AND dt2.dno != 'request_close' ");
		if(companyNos!=null&&companyNos.length>0){
			sql.append("AND r2.companyNo IN (");
			setCompanyNos(companyNos, sql);
			sql.append(") ");
		}
		sql.append("GROUP BY tu2.loginName) c ");
		sql.append("ON a.loginName = c.loginName ");
		return sql;
	}
	/**
	 * 构造请求统计待处理请求数Sql
	 * @param sql
	 * @return StringBuffer
	 */
	private StringBuffer setSqlByNewleCount(Long[] companyNos,StringBuffer sql){
		sql.append("LEFT JOIN ");
		sql.append("(SELECT tu2.loginName AS loginName, COUNT(DISTINCT r2.eno) AS newCoun FROM Request r2 ");
		//sql.append("LEFT JOIN Event e2 ON r2.eno=e2.eno ");
		sql.append("LEFT JOIN DataDictionaryItems dt2 ON r2.status_dcode=dt2.dcode ");
		sql.append("RIGHT JOIN T_User tu2 ON r2.technician_userId=tu2.userId ");
		sql.append("WHERE dt2.dno='request_new' ");
		if(companyNos!=null&&companyNos.length>0){
			sql.append("AND r2.companyNo IN (");
			setCompanyNos(companyNos, sql);
			sql.append(") ");
		}
		sql.append("GROUP BY tu2.loginName) d ");
		sql.append("ON a.loginName = d.loginName ");
		return sql;
	}
	/**
	 * 构造请求统计服务目录总分数Sql
	 * @param sql
	 * @return StringBuffer
	 */
	private StringBuffer setSqlByScoresSum(Long[] companyNos,StringBuffer sql){
		sql.append("LEFT JOIN ");
		sql.append("(SELECT tu2.loginName AS loginName, SUM(ec.scores) AS scores  FROM Request r2 ");
		//sql.append("LEFT JOIN Event e2 ON r2.eno=e2.eno ");
		sql.append("LEFT JOIN DataDictionaryItems dt2 ON r2.status_dcode=dt2.dcode ");
		sql.append("LEFT JOIN Request_EventCategory rec ON r2.eno=rec.Request_eno ");
		sql.append("LEFT JOIN EventCategory ec ON rec.serviceDirectory_EventId=ec.EventId ");
		sql.append("RIGHT JOIN T_User tu2 ON r2.technician_userId=tu2.userId ");
		sql.append("WHERE (dt2.dno != 'request_new' AND dt2.dno != 'request_complete' AND dt2.dno != 'request_close') OR dt2.dno='request_new' ");
		if(companyNos!=null&&companyNos.length>0){
			sql.append("AND r2.companyNo IN (");
			setCompanyNos(companyNos, sql);
			sql.append(") ");
		}
		sql.append("GROUP BY tu2.loginName) e ");
		sql.append("ON a.loginName = e.loginName ");
		return sql;
	}
	
	/**
	 * sql 排序
	 * @param sql
	 * @param sidx
	 * @param sord
	 */
	private StringBuffer sortHql(StringBuffer sql,String sidx,String sord){
		if ("fullName".equals(sidx)) {
			sql.append("ORDER BY a.fullName ");
		} else if ("loginName".equals(sidx)) {
			sql.append("ORDER BY a.loginName ");
		} else if ("servicesTotalScore".equals(sidx)) {
			sql.append("ORDER BY e.scores ");
		} else if("countAssignedMineRquest".equals(sidx)){
			sql.append("ORDER BY b.allCoun ");
		}else if("countMineHandingRequest".equals(sidx)){
			sql.append("ORDER BY c.handleCoun ");
		}else if("countMineNewRequest".equals(sidx)){
			sql.append("ORDER BY d.newCoun ");
		}
		if ("desc".equals(sord)) {
			sql.append(" DESC");
		} else {
			sql.append(" ASC");
		}
		return sql;
	}

	/**
	 * 根据公司分组统计
	 * 
	 * @param qdto
	 * @return 公司分组统计结果
	 */
	@SuppressWarnings("unchecked")
	public List<StatResultDTO> groupStatRequest(RequestQueryDTO qdto) {
		DetachedCriteria dc = getAllRequestDetachedCriteria(qdto);
		dc.setProjection(Projections.projectionList()
				.add(Projections.groupProperty("companyNo").as("id"))
				.add(Projections.alias(Projections.rowCount(), "quantity")));

		dc.setResultTransformer(Transformers.aliasToBean(StatResultDTO.class));
		List<StatResultDTO> statResult = (List<StatResultDTO>) getHibernateTemplate()
				.findByCriteria(dc);
		return statResult;
	}

	/**
	 * 查询分类
	 * 
	 * @param qdto
	 * @param dc
	 * @return
	 */
	private DetachedCriteria searchCategory(RequestQueryDTO qdto,
			DetachedCriteria dc) {
		if(qdto.getEcategoryNo()!=null && qdto.getCategoryNos() != null && qdto.getCategoryNos().length > 0){
			dc.add(Restrictions.in("rc.eventId", qdto.getCategoryNos()));
		}else if(qdto.getCategoryNos() != null && qdto.getCategoryNos().length > 0) {// 分类
			dc.add(Restrictions.or(
					Restrictions.in("rc.eventId", qdto.getCategoryNos()),
					Restrictions.isNull("rc.eventId")));
		}
		return dc;
	}

	/**
	 * 查询符合升级条件的请求
	 * 
	 * @return List<Request>
	 */
	@SuppressWarnings("unchecked")
	public List<Request> findEligibleRequests(Long contractNo,
			Long responseTime, Long completeTime) {
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		dc.createAlias("slaRule", "sr").createAlias("sr.slaContract", "sc")
				.add(Restrictions.eq("sc.contractNo", contractNo));
		if (responseTime != null) {
			dc.add(Restrictions.isNull("responsesTime"));
			dc.add(Restrictions.le("maxResponseTime", responseTime));
		}
		if (completeTime != null) {
			dc.add(Restrictions.isNull("closeTime"));
			dc.add(Restrictions.le("maxCompleteTime", completeTime));
		}
		dc.add(Restrictions.eq("hang", false));// 非挂起
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	
	/**
	 * 查询关联表单的请求数量
	 * @param formId
	 * @return
	 */
	public Integer findRquestNumByFormId(Long[] formIds){
		Integer num = 0;
		DetachedCriteria dc = DetachedCriteria.forClass(Request.class);
		if(formIds!=null && formIds.length >0 ){
			dc.add(Restrictions.in("formId", formIds));
		}
		List list = super.getHibernateTemplate().findByCriteria(dc);
		if(list!=null){
			num = list.size();
		}
		return num;
	}

	public int insertAttr(String hql) {
		//System.err.println(hql);
		return super.getHibernateTemplate().excuteSQL(hql);
	}
	public Map findByCustom(Long eno,Boolean export) {
		Field f =new Field();
		f.setModule("request");
		List<Field> list= fieldDAO.findListField(f,export);
		if (list != null && list.size()>0) {
			StringBuffer str=new StringBuffer();
			/*for (int i = 0; i < list.size(); i++) {
				str.append(",?");
			}*/
			for (Field field : list) {
	           // query.setParameter(i, field.getName());
	            //query.addScalar(field.getName());
	            str.append(","+field.getName());
			} 
			String hql="select "+str.toString().substring(1)+" from cu_request where eno="+eno;
			SQLQuery query = super.getSession().createSQLQuery(hql);
			query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			Map map=(Map)query.uniqueResult();
/*			try {
				 System.err.println(JSONUtil.serialize(map));
			} catch (Exception e) {
			}
*/	        return map;
        }
		return null;
	}
	/**
	 * 统计请求分类报表
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List requestCatagoryReport(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT e.EventName, COUNT(eno)as num FROM cu_request r LEFT JOIN EventCategory e ON r.categoryNo=e.EventId GROUP BY r.categoryNo ");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 统计请求状态报表
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List requestStatusReport(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT d.dname, COUNT(eno) FROM cu_request r LEFT JOIN datadictionaryitems d ON r.status_dcode=d.dcode GROUP BY r.status_dcode");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 统计SLA响应率报表
	 * @return
	 */
	public List requestSLAStatusReport(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(eno) FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode ");
		sql.append("UNION ALL");
		sql.append(" SELECT count(eno) FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode WHERE d.dno in ('ToBeResponded','ToBeRespondedOverdue','ToBeRespondedOverdue&ToBeSettledOverdue') ");
		sql.append("UNION ALL");
		sql.append(" SELECT count(eno) FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode WHERE d.dno in ('RespondedOnTime&ToBeSettled','RespondedOnTime&ToBeSettledOverdue','RespondedOnTime&SettledOverdue','RespondedOnTime&SettledOnTime') ");
		sql.append("UNION ALL");
		sql.append(" SELECT count(eno) FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode WHERE d.dno in ('RespondedOverdue&ToBeSettled','RespondedOverdue&ToBeSettledOverdue','RespondedOverdue&SettledOverdue','RespondedOverdue&SettledOnTime')");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 统计SLA完成率报表
	 * @return
	 */
	public List requestSLACompleteReport(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(eno) FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode ");
		sql.append("UNION ALL");
		sql.append(" SELECT count(eno)  FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode WHERE d.dno in ('RespondedOnTime&SettledOnTime','RespondedOverdue&SettledOnTime') ");
		sql.append("UNION ALL");
		sql.append(" SELECT count(eno)  FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode WHERE d.dno in ('RespondedOnTime&SettledOverdue','RespondedOverdue&SettledOverdue') ");
		sql.append("UNION ALL");
		sql.append(" SELECT count(eno)  FROM cu_request r LEFT JOIN datadictionaryitems d ON r.slaState_dcode=d.dcode WHERE d.dno in ('ToBeRespondedOverdue&ToBeSettledOverdue','RespondedOverdue&ToBeSettled','RespondedOverdue&ToBeSettledOverdue','RespondedOnTime&ToBeSettled','RespondedOnTime&ToBeSettledOverdue')");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 技术员工作量统计
	 * @return
	 */
	public List engineerWorkloadReport(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(r.eno) AS rcount,u.fullName AS fullName,d.dname AS dname FROM cu_request r LEFT JOIN t_user u ON r.technician_userId=u.userId LEFT JOIN datadictionaryitems d ON r.status_dcode=d.dcode WHERE 1=1 GROUP BY d.dname,u.fullName");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 技术员忙碌程度统计
	 * @return
	 */
	public List workloadStatusStatistics(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tu.loginName AS loginName,tu.fullName AS fullName,(SELECT COUNT(r.eno) FROM cu_request r LEFT JOIN DataDictionaryItems d ON r.status_dcode=d.dcode WHERE r.technician_userId=tu.userId AND r.responsesTime IS NOT NULL AND r.closeTime IS NULL AND d.dno NOT IN ('request_approval')) AS inporgress,");
        sql.append("(SELECT COUNT(r.eno) FROM cu_request r LEFT JOIN DataDictionaryItems d ON r.status_dcode=d.dcode WHERE r.technician_userId=tu.userId AND r.responsesTime IS NOT NULL AND r.closeTime IS NULL AND d.dno IN ('request_approval')) AS awaiting,");
		sql.append("(SELECT COUNT(r.eno) FROM cu_request r  WHERE r.technician_userId=tu.userId AND r.closeTime IS NOT NULL) AS requestclose,");
		sql.append("(SELECT  COUNT(r.eno) FROM cu_request r LEFT JOIN T_User t ON r.technician_userId=t.userId LEFT JOIN DataDictionaryItems d ON r.status_dcode=d.dcode WHERE t.userId=tu.userId) AS hold,");
		sql.append("(SELECT SUM(ec.scores) FROM cu_request r LEFT JOIN T_User t ON r.technician_userId=t.userId LEFT JOIN cu_request_eventcategory re ON r.eno=re.cu_request_eno LEFT JOIN EventCategory ec ON re.serviceDirectory_EventId=ec.EventId WHERE t.userId=tu.userId AND r.closeTime IS NULL) AS scores ");
		sql.append(" FROM cu_request rq LEFT JOIN T_User tu ON rq.technician_userId=tu.userId WHERE tu.loginName IS NOT NULL GROUP BY tu.loginName,tu.fullName,tu.userId");
		System.err.println(sql);
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 技术员处理请求耗时
	 * @return
	 */
	public List tctakeTimeperRequest(){
		StringBuffer sql = new StringBuffer();
		sql.append("select rq.etitle as etitle,us.fullName as loginName,rq.responsesTime as responsesTime,rq.closeTime as requestResolvedTime from cu_request rq LEFT JOIN  t_user us ON rq.technician_userId=us.userId LEFT JOIN DataDictionaryItems d ON rq.status_dcode=d.dcode");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
	/**
	 * 技术员工时统计报表
	 * @return
	 */
	public List technicianCostTime(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tu.fullName AS fullName,SUM(ps.startToEedTime)/60 AS ecHour,SUM(ps.actualTime)/60 AS costHour ,MIN(ps.startTime) AS startTime,MAX(ps.endTime) AS endTime,COUNT(rq.eno) AS rquestCount FROM cu_request rq LEFT JOIN  Progress ps ON rq.eno=ps.eno LEFT JOIN T_User tu ON rq.technician_userId=tu.userId GROUP BY tu.fullName");
		return super.findPageBySql(sql.toString(), 0, 10000).getData();
	}
}
